﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;

namespace Test.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class PersonService
    {
        [OperationContract]
        public void ResetDatabase()
        {
        }

        [OperationContract]
        public Department AddDepartment(Department department)
        {
            object result;
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("insert into Department(Name) values (@name); select SCOPE_IDENTITY()", connection))
                {
                    command.Parameters.Add("@name", System.Data.SqlDbType.NVarChar).Value = department.Name;
                    result = command.ExecuteScalar();
                }
            }
            department.Id = Convert.ToInt32((decimal)result);
            return department;
        }

        [OperationContract]
        public int UpdateDepartment(Department department)
        {
            object result;
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("update Department set Name=@name where ID=@id; select @@rowcount", connection))
                {
                    command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = department.Id;
                    command.Parameters.Add("@name", System.Data.SqlDbType.NVarChar).Value = department.Name;
                    result = command.ExecuteScalar();
                }
            }
            return (int)result;
        }

        [OperationContract]
        public int DeleteDepartment(Department department)
        {
            object result = null;
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("delete from Department where ID = @id;select @@rowcount", connection))
                {
                    command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = department.Id;
                    result = command.ExecuteScalar();
                }
            }
            return (int)result;
        }

        [OperationContract]
        public int DeletePerson(Person person)
        {
            object result = null;
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("delete from Person where ID = @id;select @@rowcount", connection))
                {
                    command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = person.Id;
                    result = command.ExecuteScalar();
                }
            }
            return (int)result;
        }

        [OperationContract]
        public Person AddPerson(Person person)
        {
            object result;
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("insert into Person (FirstName, LastName, DepartmentID) values (@firstName, @lastName, @departmentID); select @@identity", connection))
                {
                    command.Parameters.Add("@firstName", System.Data.SqlDbType.NVarChar).Value = person.FirstName;
                    command.Parameters.Add("@lastName", System.Data.SqlDbType.NVarChar).Value = person.LastName;
                    command.Parameters.Add("@departmentID", System.Data.SqlDbType.NVarChar).Value = person.DepartmentId;
                    result = command.ExecuteScalar();
                }
            }
            person.Id = Convert.ToInt32((decimal)result);
            return person;
        }

        [OperationContract]
        public int UpdatePerson(Person person)
        {
            object result;
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("update Person set FirstName=@firstName, LastName=@lastName, DepartmentID=@departmentId where ID=@id; select @@rowcount", connection))
                {
                    command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = person.Id;
                    command.Parameters.Add("@firstName", System.Data.SqlDbType.NVarChar).Value = person.FirstName;
                    command.Parameters.Add("@lastName", System.Data.SqlDbType.NVarChar).Value = person.LastName;
                    command.Parameters.Add("@departmentId", System.Data.SqlDbType.Int).Value = person.DepartmentId;
                    result = command.ExecuteScalar();
                }
            }
            return (int)result;
        }

        [OperationContract]
        public List<Person> GetPersons()
        {
            var persons = new List<Person>();
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("select ID, FirstName, LastName, DepartmentID from Person", connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            persons.Add(new Person { Id = reader.GetInt32(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), DepartmentId = reader.GetInt32(3) });
                    }
                }
            }
            return persons;
        }

        [OperationContract]
        public List<Department> GetDepartments()
        {
            var departments = new List<Department>();
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("select ID, Name from Department", connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            departments.Add(
                                new Department
                                {
                                    Id = reader.GetInt32(0),
                                    Name = reader.GetString(1)
                                }
                            );
                        }
                    }
                }
            }
            return departments;
        }

        private Department GetDepartment(int id)
        {
            Department department = null;
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("select ID, Name from Department where ID=@id", connection))
                {
                    command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            department = new Department
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1)
                            };
                        }
                    }
                }
            }
            return department;
        }

        [OperationContract]
        public List<Department> GetDepartmentByName(string namePart)
        {
            var departments = new List<Department>();
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("select ID, name from Department where Name like '%' + @namePart + '%'", connection))
                {
                    command.Parameters.Add("@namePart", System.Data.SqlDbType.NVarChar).Value = namePart;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            departments.Add(
                                new Department
                                {
                                    Id = reader.GetInt32(0),
                                    Name = reader.GetString(1)
                                }
                            );
                        }
                    }
                }
            }
            return departments;
        }

        [OperationContract]
        public List<Person> GetPersonsByName(string namePart)
        {
            var persons = new List<Person>();
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("select ID, FirstName, LastName, DepartmentID from Person where FirstName like '%' + @namePart + '%' or LastName like '%' + @namePart + '%'", connection))
                {
                    command.Parameters.Add("@namePart", System.Data.SqlDbType.NVarChar).Value = namePart;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            persons.Add(
                                new Person
                                {
                                    Id = reader.GetInt32(0),
                                    FirstName = reader.GetString(1),
                                    LastName = reader.GetString(2),
                                    DepartmentId = reader.GetInt32(3)
                                }
                            );
                        }
                    }
                }
            }
            return persons;
        }

        [OperationContract]
        public List<Person> GetPersonsByDepartment(int departmentId)
        {
            var persons = new List<Person>();
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("select ID, FirstName, LastName, DepartmentID from Person where DepartmentID = @departmentId", connection))
                {
                    command.Parameters.Add("@departmentId", System.Data.SqlDbType.Int).Value = departmentId;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            persons.Add(
                                new Person
                                {
                                    Id = reader.GetInt32(0),
                                    FirstName = reader.GetString(1),
                                    LastName = reader.GetString(2),
                                    DepartmentId = reader.GetInt32(3)
                                }
                            );
                        }
                    }
                }
            }
            return persons;
        }

    }

    [DataContract]
    public class Person
    {
        [DataMember]
        public int Id { get; set; }
        [DataMember]
        public string FirstName { get; set; }
        [DataMember]
        public string LastName { get; set; }
        [DataMember]
        public int DepartmentId { get; set; }
    }

    [DataContract]
    public class Department
    {
        [DataMember]
        public int Id { get; set; }
        [DataMember]
        public string Name { get; set; }
    }
}
